if exists (select 1 from sysobjects where name = 'get_personeninschema' and type = 'P')
begin
   drop procedure get_personeninschema
   print 'Procedure: get_personeninschema deleted ...'
end
go
create procedure get_personeninschema(
  @schemaid            int = 9
)
as
begin
  set nocount on

   create table #prs(
       prsid         int,
       name          varchar(32),
       nachname      varchar(32),
       abteilungid   int,
       abteilungname varchar(32),
       prsschemaid   int,
       --von           datetime,
       --bis           datetime,
       rgb           int,
       --pensum        int
       vorname       varchar(32)
   )
    
   insert #prs
   select prsid         = isnull(ps.PrsID,0),
          name          = isnull(Vorname,''),
          nachname      = isnull(Nachname,''),
          abteilungid   = a.AbteilungID,
          abteilungname = a.Name,
          prsschemaid   = PrsSchemaID,
          --von           = ap.Von,
          --bis           = ap.Bis,
          rgb           = ps.RGB,
          --pensum        = isnull(Prozent,0)
          vorname       = isnull(Vorname,'')
      from PlanSchema ps
     inner join Person p
        on p.PrsId = ps.PrsID
     inner join Abteilung a
        on a.AbteilungID = ps.AbteilungID
     where ps.SchemaID = @schemaid

   select * 
     from #prs
    order by abteilungid, prsschemaid, prsID

end
go
print 'Procedure: get_personeninschema done ...'
go

grant exec on get_personeninschema to prsadmins with grant option
go
grant exec on get_personeninschema to prsusers
go

